package offer_board.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class BoardDAO {
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	
	public BoardDAO() {
		try{
			Context init = new InitialContext();
			Context envCtx = (Context) init.lookup("java:comp/env");
			DataSource ds = (DataSource)envCtx.lookup("jdbc/dbcp");
	  		con = ds.getConnection();
		}catch(Exception ex){
			System.out.println("DB 접속 에러 : " + ex);
			return;
		}
	}
	
	//baisic 글갯수
	public int getListCount() {
		int x= 0;
		
		try{
			pstmt=con.prepareStatement("select count(*) from offer_board where style_type='basic'");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			System.out.println("getListCount 에러: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return x;
	}
	//office 글개수 
	public int getListOfficeCount() {
		int x= 0;
		
		try{
			pstmt=con.prepareStatement("select count(*) from offer_board where style_type='office'");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			System.out.println("getListOfficeCount 에러: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return x;
	}
	//casual 글개수
	public int getListCasualCount() {
		int x= 0;
		
		try{
			pstmt=con.prepareStatement("select count(*) from offer_board where style_type='casual'");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			System.out.println("getListCasualCount 에러: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return x;
	}
	//list글	
	public List getBoardList(int page,int limit){
		
		/*
		 * select * from (select rownum rnum,num,writer,style_type,product_num,cody01,cody02,cody03,board_file,tags,reg_date from(select * from offer_board where style_type='basic' order by num desc)) where rnum>=? and rnum<=?
		 * */
		String board_list_sql="select * from (select rownum rnum,num,writer,style_type,product_num,cody01,cody02,cody03,board_file,tags,reg_date from(select * from offer_board where style_type='basic' order by num desc)) where rnum>=? and rnum<=?";
		
		List list = new ArrayList();
		
		int startrow=(page-1)*10+1; //�б� ������ row ��ȣ.
		int endrow=startrow+limit-1; //���� ������ row ��ȣ.		
		try{
			pstmt = con.prepareStatement(board_list_sql);
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				BoardBean board = new BoardBean();
				board.setNum(rs.getInt("num"));
				board.setWriter(rs.getString("writer"));
				board.setStyle_type(rs.getString("style_type"));
				board.setProduct_num(rs.getString("product_num"));
				board.setCody01(rs.getString("cody01"));
				board.setCody02(rs.getString("cody02"));
				board.setCody03(rs.getString("cody03"));
				board.setBoard_file(rs.getString("board_file"));
				board.setTags(rs.getString("tags"));
				board.setReg_date(rs.getDate("reg_date"));
				list.add(board);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("getBoardList 에러 : " + ex);
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	//list_office
	public List getBoardListOffice(int page,int limit){
		String board_list_sql="select * from offer_board where style_type='office' order by num desc";
		
		List list = new ArrayList();
		
		int startrow=(page-1)*10+1; //�б� ������ row ��ȣ.
		int endrow=startrow+limit-1; //���� ������ row ��ȣ.		
		try{
			pstmt = con.prepareStatement(board_list_sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				BoardBean board = new BoardBean();
				board.setNum(rs.getInt("num"));
				board.setWriter(rs.getString("writer"));
				board.setStyle_type(rs.getString("style_type"));
				board.setProduct_num(rs.getString("product_num"));
				board.setCody01(rs.getString("cody01"));
				board.setCody02(rs.getString("cody02"));
				board.setCody03(rs.getString("cody03"));
				board.setBoard_file(rs.getString("board_file"));
				board.setTags(rs.getString("tags"));
				board.setReg_date(rs.getDate("reg_date"));
				list.add(board);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("getBoardList 에러 : " + ex);
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	//casual
	public List getBoardListCasual(int page,int limit){
		String board_list_sql="select * from offer_board where style_type='casual' order by num desc";
		
		List list = new ArrayList();
		
		int startrow=(page-1)*10+1; //�б� ������ row ��ȣ.
		int endrow=startrow+limit-1; //���� ������ row ��ȣ.		
		try{
			pstmt = con.prepareStatement(board_list_sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				BoardBean board = new BoardBean();
				board.setNum(rs.getInt("num"));
				board.setWriter(rs.getString("writer"));
				board.setStyle_type(rs.getString("style_type"));
				board.setProduct_num(rs.getString("product_num"));
				board.setCody01(rs.getString("cody01"));
				board.setCody02(rs.getString("cody02"));
				board.setCody03(rs.getString("cody03"));
				board.setBoard_file(rs.getString("board_file"));
				board.setTags(rs.getString("tags"));
				board.setReg_date(rs.getDate("reg_date"));
				list.add(board);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("getBoardListCasual 에러 : " + ex);
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	//�� ���� ����.
	public BoardBean getDetail(String product_num) throws Exception{
		BoardBean board = null;
		try{
			System.out.println("sql문 시작 전");
			pstmt = con.prepareStatement(
					"select * from offer_board where product_num = ?");
			pstmt.setString(1, product_num);
			
			rs= pstmt.executeQuery();
		
			if(rs.next()){
				board = new BoardBean();
				board.setNum(rs.getInt("num"));
				board.setWriter(rs.getString("writer"));
				board.setStyle_type(rs.getString("style_type"));
				board.setProduct_num(rs.getString("product_num"));
				board.setCody01(rs.getString("cody01"));
				board.setCody02(rs.getString("cody02"));
				board.setCody03(rs.getString("cody03"));
				board.setBoard_file(rs.getString("board_file"));
				board.setTags(rs.getString("tags"));
				board.setReg_date(rs.getDate("reg_date"));
			}
			System.out.println(board.getWriter());
			System.out.println("getDetail" + board);
			return board;
		}catch(Exception ex){
			System.out.println("getDetail 에러 : " + ex);
		}finally{
			if(rs!=null)try{rs.close();}catch(SQLException ex){}
			if(pstmt !=null)try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	
	//데이터 삽입
	public boolean boardInsert(BoardBean board){
		int num =0;
		String sql="";
		
		int result=0;
		
		try{
			pstmt=con.prepareStatement("select max(num) from offer_board");
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				System.out.println(rs.getInt(1));
				num =rs.getInt(1)+1;
			}
			else
				num=1;
			System.out.println("aaaaa2");
			
			sql="insert into offer_board" +
					"(num,writer,style_type,product_num,cody01,cody02,cody03,board_file,tags,reg_date) " +
					"values(?,?,?,?,?,?,?,?,?,sysdate)";
			
				

			pstmt = con.prepareStatement(sql);
			System.out.println("aaaaa");
			pstmt.setInt(1, num);
			pstmt.setString(2, board.getWriter());
			pstmt.setString(3, board.getStyle_type());
			pstmt.setString(4, board.getProduct_num());
			pstmt.setString(5, board.getCody01());
			pstmt.setString(6, board.getCody02());
			pstmt.setString(7, board.getCody03());
			pstmt.setString(8, board.getBoard_file());
			pstmt.setString(9, board.getTags());
			
			result=pstmt.executeUpdate();
			if(result==0)return false;
			
			return true;
		}catch(Exception ex){
			System.out.println("boardInsert ���� : "+ex);
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return false;
	}
	/*
	//�� �亯.
	public int boardReply(BoardBean board){
		String board_max_sql="select max(board_num) from boardex";
		String sql="";
		int num=0;
		int result=0;
		
		int re_ref=board.getBOARD_RE_REF();
		int re_lev=board.getBOARD_RE_LEV();
		int re_seq=board.getBOARD_RE_SEQ();
		
		try{
			pstmt=con.prepareStatement(board_max_sql);
			rs = pstmt.executeQuery();
			if(rs.next())num =rs.getInt(1)+1;
			else num=1;
			
			sql="update boardex set BOARD_RE_SEQ=BOARD_RE_SEQ+1 where BOARD_RE_REF=? ";
			sql+="and BOARD_RE_SEQ>?";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,re_ref);
			pstmt.setInt(2,re_seq);
			result=pstmt.executeUpdate();
			
			re_seq = re_seq + 1;
			re_lev = re_lev+1;
			
			sql="insert into boardex (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,";
			sql+="BOARD_CONTENT, BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,BOARD_RE_SEQ,";
			sql+="BOARD_READCOUNT,BOARD_DATE) values(?,?,?,?,?,?,?,?,?,?,sysdate)";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, num);
			pstmt.setString(2, board.getBOARD_NAME());
			pstmt.setString(3, board.getBOARD_PASS());
			pstmt.setString(4, board.getBOARD_SUBJECT());
			pstmt.setString(5, board.getBOARD_CONTENT());
			pstmt.setString(6, ""); //���忡�� ������ ���ε����� ����.
			pstmt.setInt(7, re_ref);
			pstmt.setInt(8, re_lev);
			pstmt.setInt(9, re_seq);
			pstmt.setInt(10, 0);
			pstmt.executeUpdate();
			return num;
		}catch(SQLException ex){
			System.out.println("boardReply ���� : "+ex);
		}finally{
			if(rs!=null)try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
		}
		return 0;
	}
	
	//�� ����.
	public boolean boardModify(BoardBean modifyboard) throws Exception{
		String sql="update boardex set BOARD_SUBJECT=?,BOARD_CONTENT=? where BOARD_NUM=?";
		
		try{
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, modifyboard.getBOARD_SUBJECT());
			pstmt.setString(2, modifyboard.getBOARD_CONTENT());
			pstmt.setInt(3, modifyboard.getBOARD_NUM());
			pstmt.executeUpdate();
			return true;
		}catch(Exception ex){
			System.out.println("boardModify ���� : " + ex);
		}finally{
			if(rs!=null)try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
			}
		return false;
	}
	*/
	//글삭제
	public boolean boardDelete(String product_num){
		System.out.println("boardDelete 에 있는 sql문 실행 전");
		String board_delete_sql="delete from offer_board where product_num=?";
		
		int result=0;
		
		try{
			pstmt=con.prepareStatement(board_delete_sql);
			pstmt.setString(1, product_num);
			
			result=pstmt.executeUpdate();
			if(result==0)return false;
			
			return true;
		}catch(Exception ex){
			System.out.println("boardDelete 에러 : "+ex);
		}finally{
			try{
				if(pstmt!=null)pstmt.close();
			}catch(Exception ex) {}
		}
		
		return false;
	}
	/*
	//��ȸ�� ������Ʈ.
	public void setReadCountUpdate(int num) throws Exception{
		String sql="update boardex set BOARD_READCOUNT = "+
			"BOARD_READCOUNT+1 where BOARD_NUM = "+num;
		
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("setReadCountUpdate ���� : "+ex);
		}
	}
	
	//�۾������� Ȯ��.
	public boolean isBoardWriter(int num,String pass){
		String board_sql="select * from boardex where BOARD_NUM=?";
		
		try{
			pstmt=con.prepareStatement(board_sql);
			pstmt.setInt(1, num);
			rs=pstmt.executeQuery();
			rs.next();
			
			if(pass.equals(rs.getString("BOARD_PASS"))){
				return true;
			}
		}catch(SQLException ex){
			System.out.println("isBoardWriter ���� : "+ex);
		}
		return false;
	}
	*/

}
	